import pymysql
import os

class UserManager:
    def __init__(self):
        self.mysql_pwd = os.getenv('MYSQL_PWD', "123456")
        self.db_name = os.getenv('DB_NAME', "appDB")

    def get_db_connection(self):
        """获取数据库连接"""
        return pymysql.connect(
            host="localhost",
            user="root",
            password=self.mysql_pwd,
            database=self.db_name,
            charset='utf8'
        )

    def get_restaurants(self):
        """获取所有餐厅信息"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM RESTAURANT"
            cursor.execute(sql)
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_dishes_by_restaurant(self, restaurant):
        """根据餐厅名称获取菜品信息"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM DISHES WHERE restaurant = %s"
            cursor.execute(sql, (restaurant,))
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_specialty_dishes(self, restaurant):
        """根据餐厅名称获取特色菜信息"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM DISHES WHERE restaurant = %s AND isSpecialty = 1"
            cursor.execute(sql, (restaurant,))
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_dishes_sorted_by_sales(self, restaurant):
        """根据销量排序获取菜品信息"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM DISHES WHERE restaurant = %s ORDER BY sales DESC"
            cursor.execute(sql, (restaurant,))
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_dishes_sorted_by_price(self, restaurant):
        """根据价格排序获取菜品信息"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM DISHES WHERE restaurant = %s ORDER BY price DESC"
            cursor.execute(sql, (restaurant,))
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_shopping_cart(self):
        """获取购物车内容"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            cursor.execute("USE appDB")
            sql = "SELECT * FROM SHOPPINGCART"
            cursor.execute(sql)
            res = cursor.fetchall()
            return res if res else None
        except pymysql.Error as e:
            print(f"Database error: {e}")
            return None
        finally:
            if db:
                db.close()